const db = require('../db/connMysql');

const censusDao = {
    async getAllClassesList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               c.classId as classId,
                               f.name as college,
                               g.name as grade,
                               c.name as className,
                               c.major as major,
                               c.teacherId as counselorId,
                               t.uid as counselorUid,
                               u.username as counselor,
                               u.phone as counselorConcatIphone,
                               c.level as level
                           from Classes as c
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Grades as g on c.gradeId = g.gradeId
                           left join Teachers as t on c.teacherId = t.teacherId
                           left join Users as u on t.uid = u.uid
                           where c.isDel = 'false'`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    async updateClasses(classId, className, gradeId, facultyId, level, teacherId, major) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `update Classes
                           set
                               facultyId=?,
                               gradeId=?,
                               name=?,
                               major=?,
                               teacherId=?,
                               level=?
                           where classId=?`;
                let data = [facultyId, gradeId, className, major, teacherId, level, classId];
                connection.query(sql, data, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    async delClasses(classId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `update Classes set isDel = 'true', name = CONCAT(name, '-', classId) where classId = ?`;
                connection.query(sql, [classId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    async addClasses(className, gradeId, facultyId, level, teacherId, major) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `insert into Classes(
                               facultyId, gradeId, name,
                               major, teacherId, level, isDel
                           ) values (?, ?, ?, ?, ?, ?, 'false')`;
                connection.query(sql, [facultyId, gradeId, className, major, teacherId, level], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    async getAllGradeList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `SELECT gradeId, name FROM Grades ORDER BY year ASC`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    async getAllClassNameList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `SELECT name as n FROM Classes`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    }
};

module.exports = censusDao;
